[Fix] - Login failed for user ‘username’.
Scenario:
When trying to connect to the newly installed Microsoft SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005, or SQL Server 2000 the error message occurs as:
Login failed for user '%.*ls'. The login is a SQL Server login and cannot be used with Windows Authentication.%.*ls
Login failed for user '<user_name>'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
Login failed for user '<user_name>'. (Microsoft SQL Server, Error: 18456)
Fix 1 :
Follow the steps below:
1. Create new user with Administrator privileges with same username and password as of SQL Server in Windows.
2. On SQL Server database create new user as follows:
DatabaseNode -> Security -> Login -> Create New User -> Select “Add this new user with Windows Authentication” option.
This user can be only added by selected Windows Authentication it is Operating System’s User Login.
Fix 2 :
Sometimes the login for SQL Server is configured as SQL authentication, but it only accepts Windows Authentication.
Hence the best way is to use Mixed Authentication Mode for configuration of SQL Server 2014, SQL Server 2012, SQL Server 2008, and SQL Server 2005 as follows:
1. Open SQL Server Management Studio. To do this, click Start -> click All Programs -> click Microsoft SQL Server 20xx (where xx is the version of SQL) -> click SQL Server Management Studio.
2. Right-click the server, and then click Properties.
3.On the Security page, under Server authentication, click the SQL Server and Windows Authentication mode option button, and then click OK.
4. In the SQL Server Management Studio dialog box, click OK to restart SQL Server.
The exact error message may be hidden from the client for security purposes. But, in the SQL Server error log, an error contains an error state to map to an authentication failure condition. Compare the error state to with the below list to find the reason for the login failure:
| State | Description |
| 2 | User ID is not valid.
|
| 5 | User ID is not valid.
|
| 6 | An attempt was made to use a Windows login name with SQL Server Authentication.
|
| 7 | Login is disabled, and the password is incorrect.
|
| 8 | The password is incorrect.
|
| 9 | Password is not valid.
|
| 11 | Login is valid, but server access failed.
|
| 12 | Login is valid login, but server access failed.
|
| 18 | Password must be changed.
|
Fixes are applicable to the following versions of SQL Server :
- Microsoft SQL Server 2012 Standard
- Microsoft SQL Server 2012 Enterprise
- Microsoft SQL Server 2012 Developer
- Microsoft SQL Server 2012 Express
- Microsoft SQL Server 2012 Web
- Microsoft SQL Server 2008 Standard
- Microsoft SQL Server 2008 Enterprise
- Microsoft SQL Server 2008 Developer
- Microsoft SQL Server 2008 Express
- Microsoft SQL Server 2008 Workgroup
- Microsoft SQL Server 2008 Web
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Enterprise Edition
Related Error Tags:
- Error 18452 (not associated with a trusted sql server connection)
- "Login failed for user" error message when you log on to SQL Server
- Error 18452 (not associated with a trusted sql server connection
- "Not associated with a trusted SQL Server connection" error message
- Understanding the error message: “Login failed for user ”
- 'sa'. The user is not associated with a trusted SQL Server connection
- The user is not associated with a trusted SQL Server Connection
- Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
